package stendcontrol;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DBServer implements SaveLoadExp {

    private Configurator conf = Configurator.getConf();
    private Long[] id;
    private Connection con;
    private final String SHOW_TABLES = "show tables";
    private final String SET_MAX_PACKET_SIZE = "set global max_allowed_packet="
            + " 1024*1024*5 ";
    private Statement statement;
    private final String CLASS_NAME = "com.mysql.jdbc.Driver";
    private final String GET_NAMES_SQL = "Select expName , expID "
            + "from experiments";
    private final String EXP_DATA_CREATE_CODE =
            "CREATE TABLE `exp_data`("
            + "`rowID` INT(10) NOT NULL AUTO_INCREMENT,"
            + "`experiment` MEDIUMBLOB NOT NULL,"
            + "`indexNumber` INT(10) NOT NULL,"
            + "`exp_id` INT(10) NOT NULL,"
            + "PRIMARY KEY (`rowID`),"
            + "INDEX `fk_exp_id` (`exp_id`),"
            + "CONSTRAINT `fk_exp_id` FOREIGN KEY (`exp_id`) REFERENCES `experiments`"
            + "(`expID`) ON UPDATE CASCADE ON DELETE CASCADE)";
    private final String EXPERIMENTS_CREATE_CODE =
            "CREATE TABLE `experiments` ("
            + "`expID` INT(10) NOT NULL AUTO_INCREMENT,"
            + "`experimentPerformDate` DATE NOT NULL,"
            + "`expName` VARCHAR(50) NOT NULL,"
            + "PRIMARY KEY (`expID`))";
    private final String DELETE_SQL = "delete from experiments where "
            + "expID = ? ";
    private final String GET_EXP_COUNT = "select count(*) from experiments";
    private final String expDataTable = "exp_data";
    private final String experiments = "experiments";

    public Connection getCon() {
        return con;
    }

    public void createTable() {
        try {
            boolean isExistExpData = false;
            boolean isExistExps = false;
            ResultSet set = statement.executeQuery(SHOW_TABLES);
            while (set.next()) {
                if (expDataTable.equals(set.getString(1))) {
                    isExistExpData = true;
                }
                if ( experiments.equals(set.getString(1))) {
                    isExistExps = true;
                }
            }
            set.close();
            if (!isExistExps) {
                statement.execute(EXPERIMENTS_CREATE_CODE);
            }
            if (!isExistExpData) {
                 statement.execute(EXP_DATA_CREATE_CODE);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBServer.class.getName()).log(Level.SEVERE, null, ex);
        }
       
    }

    public DBServer() throws SQLException, ClassNotFoundException {
        this.createConnection();
        createTable();
    }

    public Long[] getId() {
        return id;
    }

    private void createConnection() throws SQLException, ClassNotFoundException {
        Class.forName(CLASS_NAME);
        con = DriverManager.getConnection("jdbc:mysql://"
                + conf.getProperty("db_server") + ":"
                + conf.getProperty("db_port") + "/"
                + conf.getProperty("db_name"),
                conf.getProperty("db_user"),
                conf.getProperty("db_password"));
        statement = con.createStatement();
        if (con == null) {
            StendLogger.log(StendLogger.MSG_ERROR, "Failed to connect to "
                    + "database");
        }
    }

    public void closeConnection() {
        if (con != null) {
            try {
                con.close();
                statement.close();
                System.out.println("Connection closed");
            } catch (SQLException ex) {
                Logger.getLogger(DBServer.class.getName()).
                        log(Level.SEVERE, null, ex);
            }
        } else {
            StendLogger.log(StendLogger.MSG_INFORM, "Connection is already"
                    + "closed or hasn't been established yet");
        }
    }

    @Override
    public String getNameSaver() {
        return "База Данных";
    }

    @Override
    public String[] getNamesExp() {
        ArrayList<String> rs = new ArrayList<String>();
        ArrayList<Long> ids = new ArrayList<Long>();
        String res[] = null;

        try {
            ResultSet set = statement.executeQuery(GET_NAMES_SQL);
            while (set.next()) {
                rs.add(set.getString(1));
                ids.add(set.getLong(2));
            }
            res = new String[rs.size()];
            id = new Long[rs.size()];
            for (int i = 0; i < res.length; i++) {
                res[i] = (String) rs.get(i);
                id[i] = ids.get(i);

            }
            set.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBServer.class.getName()).log(Level.SEVERE,
                    null, ex);
        }
        return res;
    }

    public void deleteExp(int exp_num) {
        try {
            String[] names = getNamesExp();
            Long[] ids = getId();
            PreparedStatement pstmt = con.prepareStatement(DELETE_SQL);
            pstmt.setInt(1, ids[exp_num].intValue());
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBServer.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    @Override
    public boolean saveExp(Experiment exp) {
        ExperimentOutputStream eos = null;
        DBOutputStream dbstream = null;
        boolean isOk = false;
        try {
            dbstream = new DBOutputStream(con, exp);
            eos = new ExperimentOutputStream(dbstream);
            if (eos != null) {
                isOk = true;
            }
            eos.writeExp(exp);
            eos.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBServer.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(DBServer.class.getName()).
                    log(Level.SEVERE, null, ex);
        } finally {
            try {
                dbstream.close();
            } catch (IOException ex) {
                Logger.getLogger(DBServer.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return isOk;
    }

    @Override
    public Experiment loadExp(int exp_num) {
        String[] names = getNamesExp();
        Long[] ids = getId();
        Experiment exp = null;
        DBInputStream distrem = null;
        try {
            distrem = new DBInputStream(con, ids[exp_num].intValue());
            ExperimentInputStream eis = new ExperimentInputStream(distrem);
            exp = eis.readExp();
        } catch (SQLException ex) {

            Logger.getLogger(DBServer.class.getName()).
                    log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(DBServer.class.getName()).
                    log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBServer.class.getName())
                    .log(Level.SEVERE, null, ex);
        } finally {
            try {
                distrem.close();
            } catch (IOException ex) {
                Logger.getLogger(DBServer.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return exp;
    }

    @Override
    public int getExpCount() {
        int res = 0;
        try {
            PreparedStatement pstmt = con.prepareStatement(GET_EXP_COUNT);
            ResultSet rs = pstmt.executeQuery();
            rs.next();
            res = rs.getInt(1);
        } catch (SQLException ex) {
            Logger.getLogger(DBServer.class.getName()).
                    log(Level.SEVERE, null, ex);
        }
        return res;
    }

    @Override
    public String toString() {
        return getNameSaver();
    }
    
    
}
